#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";
require "/var/www/cgi-bin/gnugraph/rcoos/graphAll.lib";

my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my $sensor=0; 
my $platform="";
my ($station,$standardname,$uom,$formula,@mvalue,$value,$mdate,@mtime);

my $tmp="/var/www/html/images2/";

my ($sqlsta,$sth);

my $dst=tellmeedt();

my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');
my $dbh = DBI->connect ("dbi:Pg:dbname=$db_name","$db_user","$db_passwd");
if (!defined $dbh) {die "Cannot connect to database!\n";}

my ($timein1year);
   my $sqlsta="select TO_CHAR(current_timestamp-interval '1 year','YYYY-MM-DD hh24:mi:ss')";
   my $sth = $dbh->prepare($sqlsta);
   $sth->execute();
 
   while (my @row = $sth->fetchrow_array) {
     $timein1year=substr($row[0],0,19);
   #  $timein3day=substr($row[1],0,19);
    # $timein1week=substr($row[2],0,19);
    # $timein1month=substr($row[3],0,19);
    # $timein3month=substr($row[4],0,19);
   }
   $sth->finish();

   my $sql = qq{
select m_date
,multi_obs.platform_handle
,obs_type.standard_name
,uom_type.standard_name
,m_value
,qc_level
,sensor.fixed_z
,sensor.s_order
,sensor.row_id
,platform.short_name
,m_type_display_order.row_id
from multi_obs
left join sensor on sensor.row_id=multi_obs.sensor_id
left join m_type on m_type.row_id=multi_obs.m_type_id
left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
left join platform on platform.row_id=sensor.platform_id
left join organization on organization.row_id=platform.organization_id
left join m_type_display_order on m_type_display_order.m_type_id=multi_obs.m_type_id
where 
  m_date>(now()-interval '1 year')
  order by 2,11,9,1 asc
};

   $sth = $dbh->prepare($sql);
   $sth->execute();

   my $flag=0;
   while (my @row = $sth->fetchrow_array) {

       #if($row[1] ne $platform){
        #   $platform=$row[1];
         #  $station=$row[9];
       #} # end of ne $platform

       if($row[8]!=$sensor){
            print "sensor is $sensor\n";
           if($sensor!=0){ printtofile(); } # end of sensor ne ""
           if($row[1] ne $platform){
              $platform=$row[1];
              $station=$row[9];
           }
           @mtime=();
           @mvalue=(); 

            $sensor=$row[8];
            
            ($uom,$formula)=uom_convert($row[2],$row[3]);
            my @array1=split(/_/,$row[2]);
            $standardname="";
            for(my $i=0;$i<@array1;$i++){
                 $array1[$i]=ucfirst($array1[$i]);
                 $standardname=$standardname.$array1[$i]." ";
            }
            if($row[6] && ($row[6]-0)<=0){ $standardname=$standardname." ($row[6] m)"; }
                       
        } #end of sensor change


       $value=value_convert($formula,$row[4]);  
     
       $mdate=$row[0];
       
       if(!(grep $_ eq $mdate, @mtime)) {
          
          if((!$row[5])||($row[5]!=1)){
            push(@mtime,$mdate);
            push(@mvalue,$value);
            $flag=1;
          }
       }

   } #end of next record

   if($flag==1){ printtofile();}

   undef $sth;
   $dbh->disconnect();


   sub printtofile {
             
             my $graph_data_file_1year =$tmp.'gnuplot_'.int(rand(10000000))."_1year";
             #my $graph_data_file_3day =$tmp.'gnuplot_'.int(rand(10000000))."_3day";
             #my $graph_data_file_1week =$tmp.'gnuplot_'.int(rand(10000000))."_1week";
             #my $graph_data_file_1month =$tmp.'gnuplot_'.int(rand(10000000))."_1month";
             #my $graph_data_file_3month =$tmp.'gnuplot_'.int(rand(10000000))."_3month";
       
           
             my $flag1=0; 

             print $sensor."\n";
             open(HTML_1year,">$graph_data_file_1year");
                    
             my ($time_1year);                     

             for(my $j=0;$j<@mtime;$j++){
                    my $lastUpdate=timeConvert($mtime[$j],$dst);
        
                    print HTML_1year $mtime[$j]."\t".$mvalue[$j]."\n";
                    if($flag1==0){$time_1year=$mtime[$j];}
                    $flag1=1;
                          
                 
              }#end of for time
           
              close(HTML_1year);
           
                
              my $title="$station $standardname";
              
              my $qc=0;
              my $end="'".$mtime[@mtime-1]."'";
              my $tperiod; 
              my $ticstring="";

              my @marray=('1_year');

              my $gname=$tmp.$station."_".$sensor;
              my ($newflag, $start,@temp,$graph_data_file);

              
                  $tperiod=$marray[$b];
                  my $graphname=$gname."_".$tperiod.".png";
                  if(-e $graphname){ system("rm $graphname"); }
                  
                   $newflag=$flag1;
                   $start="'$time_1year'";
                   $graph_data_file=$graph_data_file_1year;
                  
                   if($newflag==1){               
                       my $sqlsta="select timestamp $start,timestamp $start+(timestamp $end-timestamp $start)/8,timestamp $start+(timestamp $end-timestamp $start)/4,timestamp $start+3*(timestamp $end-timestamp $start)/8,timestamp $start+(timestamp $end-timestamp $start)/2,timestamp $start+5*(timestamp $end-timestamp $start)/8,timestamp $start+6*(timestamp $end-timestamp $start)/8,timestamp $start+7*(timestamp $end-timestamp $start)/8,timestamp $end";
                       
                       my $sth1 = $dbh->prepare($sqlsta);
                       $sth1->execute();
                       @temp=();
                       $ticstring="";
                       while (my @row = $sth1->fetchrow_array) {
                           for(my $b=0;$b<@row;$b++){
                             $row[$b]="'".substr($row[$b],0,16)."'";
                             if(grep $_ eq $row[$b],@temp){  ;  }
                             else {$ticstring=$ticstring.$row[$b].",";}
                           }
                        }
                        $sth1->finish();
                        $ticstring=substr($ticstring,0,-1);
                        my_graph($graph_data_file, $title, $uom, $graphname,$tperiod,$qc,$dst,$ticstring); 
                  } #end of flag=1
                  else {
                     system("cp /var/www/html/images2/nodata.png $graphname");     
                  }
 
               
}

sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}


